package darksideoftheloom;
/**
 * Used to store data for the 
 * DarkSideOfTheLoom program
 * in some persistent way.
 * @author joshuacw
 * @version 1.0
 * ChangeLog:
 * Feb 21, 2013
 * Initial Creation
 * ...
 * 3/15/2013
 * changing weave draft change into single varchars
 * per array to be parsed into boolean[][] 
 * Using DB for storage
 */
import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;



public class DataStorage {
	/*
	 * Attributes
	 */
	/**
	 * Hard coded DB name.
	 */
	private static final String DB_NAME = 
			"DarkSideOfTheLoomDB";
	
	/*
	 * list of tables and columns in DB.
	 * 
	 * table attendee(name VARCHAR(256), phone_number VARCHAR(20),
	 *			      address VARCHAR(256), email VARCHAR(256),
	 *			      username VARCHAR(256), password VARCHAR(256),
	 *			      userID VARCHAR(256) NOT NULL PRIMARY KEY)
	 * table judge(judgeID INT NOT NULL PRIMARY KEY, category VARCHAR(256),
	 *             judgeUserID VARCHAR(256) FOREIGN KEY)
	 * table organizer(userID VARCHAR(256) NOT NULL PRIMARY KEY,
	 *   			   password VARCHAR(256))
	 * table submission(SubmissionID INT NOT NULL PRIMARY KEY,
	 *  				user_name VARCHAR(256), design_name VARCHAR(256),
	 *					category VARCHAR(256), draftID INT,
	 *					submissionUserID VARCHAR(256) FOREIGN KEY)
	 * table weavedraft(SubmissionID INT NOT NULL PRIMARY KEY,
	 * 					 threadingHeight INT,
	 * 					 threadingWidth INT,
	 * 					 treadlingHeight INT,
	 * 					 treadlingWidth INT,
	 * 					 threading VARCHAR(4000),
	 * 					 tieup VARCHAR(4000),
	 * 					 treadling VARCHAR(4000) )
	 */
	
	/**
	 * prebuilt statement for inserting into attendee table
	 */
	private final String INSERT_ATTENDEE = "insert into attendee values("+
	                                      "?,?,?,?,?,?,?)";
	
	/**
	 * prebuilt statement for inserting into judge table
	 */
	private final String INSERT_JUDGE = "insert into judge values(?,?,?)";
	
	/**
	 * prebuilt statement for inserting into organizer table
	 */
	private final String INSERT_ORGANIZER = "insert into organizer " + 
	                                       "values(?,?)";
	
	/**
	 * prebuilt statement for inserting into submission table
	 */
	private final String INSERT_SUBMISSION = "insert into submission " +
	                                        "values(?,?,?,?,?,?)";
	
	/**
	 * prebuilt statement for inserting weaving drafts
	 */
	private final String INSERT_WEAVE = "insert into weavedraft " +
										"values(?,?,?,?,?,?,?,?)";

	/**
	 * prebuilt statement for selecting the organizer
	 */
	private final String SELECT_ORGANIZER = "select * from organizer";

	/**
	 * prebuilt statement for selecting the attendee
	 */
	private final String SELECT_ATTENDEES = "select * from attendee";
	
	/**
	 * prebuilt statement for selecting the submissions
	 */
	private final String SELECT_SUBMISSIONS = "select * from submission where submissionUserID = ?";
	
	/**
	 * prebuilt statement for selecting the judges
	 */
	private final String SELECT_JUDGES = "select * from judge where judgeUserID = ?";
	
	/**
	 * prebuilt statement for selecting weaves
	 */
	private final String SELECT_WEAVE = "select * from weavedraft where submissionID = ?";
	
	/**
	 * ints for easy access to the weave parts.
	 */
	private final int TIE_UP = 0;
	private final int DRAW_DOWN = 1;
	private final int THREADING = 2;
	private final int TREADLING = 3; 
	
	/*
	 * Methods
	 */
		
	
	/**
	 * connect to DB
	 * used to create the DB if it does not currently exist.
	 * @return a connection to the DB
	 * @throws SQLException 
	 */
	private Connection connectToDB() throws SQLException{
		Connection con = null;
		 File db = new File(DB_NAME);

		 //following driver setup borrowed from netbeans forums 
		 //topic 16950.html with slight modification
		 String driver = "org.apache.derby.jdbc.EmbeddedDriver"; 

		 try { 
			 Class.forName(driver);
		 } catch (ClassNotFoundException cnfe) { 
			 System.err.println("\nUnable to load the JDBC driver " + driver); 
			 System.err.println("Please check your CLASSPATH."); 
			 cnfe.printStackTrace(System.err); 
		 } 
		 //end of borrowed code


		 try{
			 if(!db.isDirectory()){
				 con = DriverManager.getConnection("jdbc:derby:" + 
				                        DB_NAME + "; create=true");
				 Statement s = con.createStatement();
				 s.executeUpdate("create table attendee(" +
				                 "name VARCHAR(256), " + 
						         "phone_number VARCHAR(20)," +
				                 "address VARCHAR(256)," +
						         "email VARCHAR(256)," +
				                 "username VARCHAR(256)," +
						         "password VARCHAR(256)," +
				                 "userID VARCHAR(256) NOT NULL PRIMARY KEY)");
				 
				 
				 s.executeUpdate("create table judge(" +
				                 "judgeID INT NOT NULL PRIMARY KEY,"+
						         "category VARCHAR(256),"+
						 		 "judgeUserID VARCHAR(256) )");
						         
				 
				 s.executeUpdate("create table organizer(" +
				                 "userID VARCHAR(256) NOT NULL PRIMARY KEY," +
						         "password VARCHAR(256))");
				 
				 s.executeUpdate("create table submission(" +
						         "SubmissionID INT NOT NULL PRIMARY KEY," +
						         "user_name VARCHAR(256)," +
						         "design_name VARCHAR(256)," +
						         "category VARCHAR(256)," +
						         "draftID INT," + 
						 		 "submissionUserID VARCHAR(256) )");
				 
				 
				 /*
				  * Java DB does not currently support the SQL ARRAY structure so this
				  * is my work around.
				  * 
				  * each boolean [][] except drawdown will be saved as a string of 1s 
				  * and 0s and in combination with the known height/width can be parsed
				  * into the array 
				  * {{0,0,1,0,1},
				  *  {1,0,0,1,0}} 
				  *  becomes
				  *  "0010110010"
				  *  as the max allowed is varchar 4000
				  *  this means that the largest size is 40X100, 50X80, ~62X62,...
				  */
				
				 s.executeUpdate("create table weavedraft(" +
						 		 "SubmissionID INT NOT NULL PRIMARY KEY," +
						  		 "threadingHeight INT," +
						  		 "threadingWidth INT," +
						  		 "treadlingHeight INT," +
						  		 "treadlingWidth INT," +
						  		 "threading VARCHAR(4000)," +
						  		 "tieup VARCHAR(4000)," +
						  		 "treadling VARCHAR(4000) )" );

				 				 
				 s.close();
			 } else {
				 con = DriverManager.getConnection("jdbc:derby:" + 
	                        DB_NAME);
			 }
		 } catch (SQLException e){
			 System.out.println("DB connection/communication error");
			 throw e;
		 }
		 return con;
	}
	
	/**
	 * save data to storage.
	 * @param attendees the attendees of the event
	 * @param organizer the organizer of the event
	 */
	public void save(List<Attendee> attendees, Organizer organizer){
		Connection con = null;
		PreparedStatement insert_cmd;
		int attend_index = 1;		
		int sub_index = 1;
		int judge_index = 1;
		try{
			con = connectToDB();
			
			/*  
             *  empty DB to repopulate the DB
			 *  this is not a good way of doing it but other objects
			 *  would have to be redesigned to hold unique ids otherwise
             */
			insert_cmd = con.prepareStatement("delete from attendee");
			insert_cmd.executeUpdate();
			insert_cmd = con.prepareStatement("delete from judge");
			insert_cmd.executeUpdate();
			insert_cmd = con.prepareStatement("delete from organizer");
			insert_cmd.executeUpdate();
			insert_cmd = con.prepareStatement("delete from submission");
			insert_cmd.executeUpdate();
			insert_cmd = con.prepareStatement("delete from weavedraft");
			insert_cmd.executeUpdate();
			
			for(Attendee a: attendees){
				//add attendee info
				insert_cmd = con.prepareStatement(INSERT_ATTENDEE);

				insert_cmd.setString(1, a.getName());
				insert_cmd.setString(2, a.getPhoneNumber());
				insert_cmd.setString(3, a.getAddress());
				insert_cmd.setString(4, a.getEmail());
				insert_cmd.setString(5, a.getUsername());
				insert_cmd.setString(6, a.getPassword());
				insert_cmd.setString(7, Integer.toString(attend_index) );
				
				
				insert_cmd.executeUpdate();
				
				int submission_index = 0;
				/*
				 *add submission info
				 */
				for(Submission s: a.getSubmissions() ){
					insert_cmd = con.prepareStatement(INSERT_SUBMISSION);
					
					submission_index = attend_index*10 + sub_index++;
					insert_cmd.setString(1, Integer.toString(submission_index) );
					insert_cmd.setString(2, s.getUserName() );
					insert_cmd.setString(3, s.getDesignName() );
					insert_cmd.setString(4, s.getCategory() );
					insert_cmd.setString(5, "0"); 
					insert_cmd.setString(6, Integer.toString(attend_index) );

					insert_cmd.executeUpdate();
					
					
					/*
					 * save weaving draft
					 * 
					 * note:
					 * drawdown is not saved as it is derived from the other data.
					 */

					WeavingDraft draft = s.getWeavingDraft();

					
					boolean[][] tieup = draft.getTieUp();
					boolean[][] threading = draft.getThreading();
					boolean[][] treadling = draft.getTreadling();
					StringBuilder data_string;
					int thread_h = 0;
					int thread_w = 0;
					int tread_h = 0;
					int tread_w = 0;
					
					if(threading != null)
						thread_h = threading.length;
					
					if(treadling != null)
						tread_h = treadling.length;
					
					if(thread_h > 0 && threading[0] != null)
						thread_w = threading[0].length;
					
					if(tread_h > 0 && treadling[0] != null)
						tread_w = treadling[0].length;
										
					/*
					 * prepare base data
					 */
					insert_cmd = con.prepareStatement(INSERT_WEAVE);
					insert_cmd.setString(1, Integer.toString(submission_index) );
					insert_cmd.setString(2, Integer.toString(thread_h) );
					insert_cmd.setString(3, Integer.toString(thread_w) );
					insert_cmd.setString(4, Integer.toString(tread_h) );
					insert_cmd.setString(5, Integer.toString(tread_w) );
					
					/*
					 * populate thread data and add to prepare statement
					 */
					data_string = new StringBuilder();
					for(int i = 0; i < thread_h; i++){
						for(int j = 0; j < thread_w; j++){
							if(threading[i][j]){
								data_string.append('1');
							}else{
								data_string.append('0');
							}
						}
					}
					insert_cmd.setString(6, data_string.toString());
					/*
					 * populate tieup data and add to prepare statement
					 */
					data_string = new StringBuilder();
					for(int i = 0; i < thread_h; i++){
						for(int j = 0; j < tread_w; j++){
							if(tieup[i][j]){
								data_string.append('1');
							}else{
								data_string.append('0');
							}
						}
					}
					insert_cmd.setString(7, data_string.toString());
					/*
					 * populate tread data and add to prepare statement
					 */
					data_string = new StringBuilder();
					for(int i = 0; i < tread_h; i++){
						for(int j = 0; j < tread_w; j++){
							if(treadling[i][j]){
								data_string.append('1');
							}else{
								data_string.append('0');
							}
						}
					}
					insert_cmd.setString(8, data_string.toString());
					
					//save weaving draft
					insert_cmd.execute();
				}
				
				//add judge entry if user is a judge
				if(a.getClass().equals("Judge") ){
					insert_cmd = con.prepareStatement(INSERT_JUDGE);
				
					insert_cmd.setString(1, Integer.toString(attend_index*10 + judge_index++) );
					insert_cmd.setString(2, ((Judge)a).getCategory() );
					insert_cmd.setString(3, Integer.toString(attend_index) );

					insert_cmd.executeUpdate();
				}

				attend_index++;
			}

			/*
			 * save organizer info
			 *
			 * table organizer(
			 *    userID VARCHAR(256) NOT NULL PRIMARY KEY,
			 *    password VARCHAR(256))
			 */
			insert_cmd = con.prepareStatement(INSERT_ORGANIZER);
			
			insert_cmd.setString(1, organizer.getUserID() );
			insert_cmd.setString(2, organizer.getPassword() );
			
			insert_cmd.executeUpdate();
			
		}catch(SQLException e){
			//try again
			System.out.println("broke in save");
			System.out.println(e.getMessage());
		}
	}
	
	/**
	 * getOrganizer
	 * get the Organizer from Storage
	 * @return the organizer of the event.
	 */
	public Organizer getOrganizer(){
		Organizer org = null;
		String org_id;
		String org_password;
		Connection con = null;
		PreparedStatement select_cmd;
		ResultSet querry_result;;

		try{		
			con = connectToDB();
			select_cmd = con.prepareStatement(SELECT_ORGANIZER);
			querry_result = select_cmd.executeQuery();

			while(querry_result.next()){
				org_id = querry_result.getString("userID");
				org_password = querry_result.getString("password");
				org = new Organizer(org_id, org_password);
			}
			

		}catch(SQLException e){
			System.out.println("broke in getOrganizer");
			System.out.println(e.getMessage() );
		}

		return org;
	}
	
	/**
	 * getAttendees
	 * get the attendees from storage
	 * @return the attendees of the event.
	 */
	public List<Attendee> getAttendees(){
		Attendee attendee_temp;
		List<Attendee> attendees = new ArrayList<Attendee>();
		Connection con = null;
		PreparedStatement select_cmd;
		ResultSet querry_result_attendee;
		ResultSet querry_result_submissions;
		ResultSet querry_result_judge;
		
		//temp attendee info
		String attendee_name;
		String attendee_pnumb;
		String attendee_address;
		String attendee_email;
		String attendee_username;
		String attendee_password;
		String attendee_uniqueID; //not used by class attendee
		ArrayList<Submission> attendee_submissions;
		
		String judge_category;
		
		String submission_uniqueID; //not used by submission class
		String submission_username;
		String submission_design_name;
		String submission_category;
		
		WeavingDraft weaving_draft;
		try{		
			con = connectToDB();
			select_cmd = con.prepareStatement(SELECT_ATTENDEES);
			querry_result_attendee = select_cmd.executeQuery();

			
			while(querry_result_attendee.next()){
				//look at each attendee
				attendee_name = querry_result_attendee.getString("name");
				attendee_pnumb = querry_result_attendee.getString("phone_number");
				attendee_address = querry_result_attendee.getString("address");
				attendee_email = querry_result_attendee.getString("email");
				attendee_username = querry_result_attendee.getString("username");
				attendee_password = querry_result_attendee.getString("password");
				attendee_uniqueID = querry_result_attendee.getString("userID");
				attendee_submissions = new ArrayList<Submission>();
				
				//get the submissions
				select_cmd = con.prepareStatement(SELECT_SUBMISSIONS);
				select_cmd.setString(1, attendee_uniqueID);
				querry_result_submissions = select_cmd.executeQuery();
				
				while(querry_result_submissions.next()){
					submission_uniqueID = querry_result_submissions.getString("SubmissionID");
					submission_username = querry_result_submissions.getString("user_name");
					submission_design_name = querry_result_submissions.getString("design_name");
					submission_category = querry_result_submissions.getString("category");
					
					weaving_draft = querryDraft(submission_uniqueID, con);
					
					attendee_submissions.add(new Submission(submission_username,
															submission_design_name,
															submission_category, 
															weaving_draft));
				}				
				//if judge
				select_cmd = con.prepareStatement(SELECT_JUDGES);
				select_cmd.setString(1, attendee_uniqueID);
				querry_result_judge = select_cmd.executeQuery();
				if(querry_result_judge.next()){
					judge_category = querry_result_judge.getString("category");
					
					//add info to attendee_temp
					attendee_temp = new Judge(attendee_name, attendee_pnumb, 
											  attendee_address, attendee_email, 
											  attendee_username, attendee_password, 
											  judge_category);
					attendee_temp.submissions = attendee_submissions;
					
					//add attendee_temp to attendees
					attendees.add(attendee_temp);
				}else{
					//add to list as attendee
					attendee_temp = new Attendee(attendee_name, attendee_pnumb, 
												 attendee_address, attendee_email, 
												 attendee_username, attendee_password);
					attendee_temp.submissions = attendee_submissions;
					
					attendees.add(attendee_temp);
				}	
			}
		}catch(SQLException e){
			System.out.println(e.getMessage() );
		}
		
		return attendees;
	}
	
	/**
	 * used for getting the weaving draft from the DB
	 * @param submissionID the id for the submission that the weave belongs to
	 * @param con the active connection with the DB where the draft is stored
	 * @throws SQLException 
	 */
	private WeavingDraft querryDraft(String submissionID, Connection con) throws SQLException{
		WeavingDraft result = null;
		int thread_h = 0;
		int thread_w = 0;
		int tread_h = 0;
		int tread_w = 0;
		String data_being_parsed;
		ResultSet db_results;
		
		PreparedStatement cmd = con.prepareStatement(SELECT_WEAVE);
		cmd.setString(1, submissionID);
		db_results = cmd.executeQuery();
		if(db_results.next()){
			thread_h = db_results.getInt(2);
			thread_w = db_results.getInt(3);
			tread_h = db_results.getInt(4);
			tread_w = db_results.getInt(5);
			
			result = new WeavingDraft(thread_h, thread_w, tread_h, tread_w);
			/*
			 * get threading
			 */
			data_being_parsed = db_results.getString(6);
			for(int i = 0; i < thread_h; i++){
				for(int j = 0; j < thread_w; j++){
					if(data_being_parsed.charAt(i*thread_w + j) == '1'){
						result.changeThreading(i, j, true);
					}else{
						result.changeThreading(i, j, false);
					}
				}
			}
			/*
			 * get tieup
			 */
			data_being_parsed = db_results.getString(7);
			for(int i = 0; i < thread_h; i++){
				for(int j = 0; j < tread_w; j++){
					if(data_being_parsed.charAt(i*tread_w + j) == '1'){
						result.changeTieUp(i, j, true);
					}else{
						result.changeTieUp(i, j, false);
					}
				}
			}
			/*
			 * get treadling
			 */
			data_being_parsed = db_results.getString(8);
			for(int i = 0; i < tread_h; i++){
				for(int j = 0; j < tread_w; j++){
					if(data_being_parsed.charAt(i*tread_w + j) == '1'){
						result.changeTreadling(i, j, true);
					}else{
						result.changeTreadling(i, j, false);
					}
				}
			}
		}
		
		return result;
	}
	
	/**
	 * used to convert a row of binary data(boolean) into an int
	 * for data compression/ ease of saving.
	 * @param the data to be converted to int form.
	 * @return the int form of the binary data
	 */
	private int convertRowIntoInt(boolean[] data){
		int result = 0;
		for(int i = 0; i < data.length; i++){
			result *= 2;
			if(data[i])
				result += 1;
		}
		return result;
	}
	
	/**
	 * used to convert a row of binary data(boolean) into an int
	 * for data compression/ ease of saving.
	 * @param data the data to be converted to binary/boolean form.
	 * @param length the length of the created boolean array.
	 * @return the boolean/binary form of the int data
	 */
	private boolean[] convertIntIntoRow(int data, int length){
		boolean[] result = new boolean[length];
		int temp = data;
		int index = result.length-1;
		
		while(temp > 0 && index >= 0){
			if(temp % 2 == 1){
				result[index] = true;
			}
			temp = temp / 2;
			index--;
		}
		
		return result;
	}
}















